﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using ECS.DAL.Entity;
using ECS.BLL.Entity;

namespace ECS.DAL.Component
{
    public class PreManifestComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT ROW_NUMBER() OVER (ORDER BY create_date) AS seq_number " +
                                          " ,[scale_id] " +
                                          " ,[location] " +
                                          " ,[display_mawb]" +
                                          " ,[display_flight_date] " +
                                          " ,[display_flight_time] " +
                                          " ,[display_flight_number] " +
                                          " ,[piece_count] " +
                                          " ,[create_date] " +
                                          " ,[display_date_time] " +
                                          " ,[display_time] " +
                                            " FROM [v_report_premanifest]" +
                                        " WHERE 1=1 ";


        private const string SQL_PARAM_FLIGHTNo = " AND [flight_number] LIKE '@flight_number%' ";
        private const string SQL_PARAM_CARRIER = " AND [carrier] LIKE '@carrier%' ";
        private const string SQL_PARAM_FLIGHT_DATE = " AND display_flight_date  = dbo.usf_format_flight_date('@flight_date') ";
        private const string SQL_PARAM_MAWB_PREFIX = " AND [mawb_prefix] LIKE '@mawbPrefix%' ";
        private const string SQL_PARAM_MAWB_SN = " AND [mawb_sn] LIKE '@mawbSN%' ";
        private const string SQL_PARAM_HAWB = " AND [hawb] LIKE '@hawb%' ";
        #endregion

        #region public properties
        
        #endregion

        #region constructor
        public PreManifestComponent()
        { 
        
        }
        #endregion

        #region private methods
        
        #endregion

        #region public mehods
        /// <summary>
        /// get Pre-manifest data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<DAL.Entity.PreManifest> GetPreManifestData(ReportCriteria parameter)
        {
            List<DAL.Entity.PreManifest> items = new List<Entity.PreManifest>();
            using (DAL.Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_QUERY);

                if (!string.IsNullOrEmpty(parameter.FlightNo))
                {
                    sqlString.Append(SQL_PARAM_FLIGHTNo);
                    sqlString.Replace("@flight_number", parameter.FlightNo);
                }
                if (!string.IsNullOrEmpty(parameter.Carrier))
                {
                    sqlString.Append(SQL_PARAM_CARRIER);
                    sqlString.Replace("@carrier", parameter.Carrier);
                }
                if (parameter.FlightDate.Ticks != 0)
                {
                    sqlString.Append(SQL_PARAM_FLIGHT_DATE);
                    sqlString.Replace("@flight_date", string.Format("{0:yyyy-MM-dd}", parameter.FlightDate));
                }
                if (!string.IsNullOrEmpty(parameter.MawbPrefix))
                {
                    sqlString.Append(SQL_PARAM_MAWB_PREFIX);
                    sqlString.Replace("@mawbPrefix", parameter.MawbPrefix);
                }
                if (!string.IsNullOrEmpty(parameter.MawbSN))
                {
                    sqlString.Append(SQL_PARAM_MAWB_SN);
                    sqlString.Replace("@mawbSN", parameter.MawbSN);
                }
                if (!string.IsNullOrEmpty(parameter.Hawb))
                {
                    sqlString.Append(SQL_PARAM_HAWB);
                    sqlString.Replace("@hawb",  parameter.Hawb);
                }
                entities.Open();
                Console.WriteLine(sqlString.ToString());
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DAL.Entity.PreManifest data = new Entity.PreManifest();
                    data.seq_number = (long)base.GetLong(0, reader);
                    data.scale_id = base.GetString(1, reader);
                    data.location = base.GetString(2, reader);
                    data.display_mawb = base.GetString(3, reader);
                    data.display_flight_date = base.GetString(4, reader);
                    data.display_flight_time  = base.GetString(5, reader);
                    data.display_flight_number = base.GetString(6, reader);
                    data.piece_count = (double)base.GetDouble(7, reader);
                    data.create_date = (DateTime)base.GetDate(8, reader);
                    data.display_date_time = base.GetString(9, reader);
                    data.display_time = base.GetString(10, reader);
                    items.Add(data);
                }
            }
            return items;
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            //using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            //{
            //    using (Entities entities = new Entities())
            //    {
            //        //insert command
            //        int m_iRecordChanged = entities.SaveChanges();
            //        if (m_iRecordChanged < 1)
            //        {
            //            throw new Exception("Manifest is not inserted.");
            //        }
            //    }
            //    transaction.Complete();
            //}
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            //using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            //{
            //    using (Entities entities = new Entities())
            //    {
            //        //update command
            //        int m_iRecordChanged = entities.SaveChanges();
            //        if (m_iRecordChanged < 1)
            //        {
            //            throw new Exception("Manifest is not updated.");
            //        }
            //    }
            //    transaction.Complete();
            //}
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            //using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            //{
            //    using (Entities entities = new Entities())
            //    {
            //        //Delete command
            //        int m_iRecordChanged = entities.SaveChanges();
            //        if (m_iRecordChanged < 1)
            //        {
            //            throw new Exception("Manifest is not deleted.");
            //        }
            //    }
            //    transaction.Complete();
            //}
        }
        #endregion
    }
}
